﻿Imports System.Data.SqlClient
Imports UTNComputers.clsSorteo
Imports UTNComputers.clsCatalogo
Imports UTNComputers.daoCatalogo
Imports UTNComputers.clsCliente
Imports UTNComputers.daoCliente
Public Class daoSorteo
    'Funcion para el alta  de Sorteo'
    Public Function Alta(ByVal Sorteo As clsSorteo) As Boolean
        Dim Cn As New SqlConnection
        Dim Cm As New SqlCommand

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_ALTA_SORTEO"

            Cm.Parameters.Add("@sort_id", SqlDbType.Int)
            Cm.Parameters.Add("@fecha", SqlDbType.SmallDateTime)
            Cm.Parameters.Add("@prod_id", SqlDbType.Int)
            Cm.Parameters.Add("@cli_id", SqlDbType.Int)
            Cm.Parameters.Add("@estado", SqlDbType.VarChar)
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)

            Cm.Parameters("@sort_id").Value = Sorteo.Prod_Id
            Cm.Parameters("@fecha").Value = Sorteo.Fecha
            Cm.Parameters("@prod_id").Value = Sorteo.Prod_Id
            Cm.Parameters("@cli_id").Value = Sorteo.Cli_Id
            Cm.Parameters("@estado").Value = Sorteo.Estado
            Cm.Parameters("@Cod_Suc").Value = Sorteo.Cod_Suc

            Cm.Connection = Cn

            Cm.ExecuteNonQuery()
            Cn.Close()
            Return True

        Catch ex As Exception
            Return False
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para la modificacion de un sorteo'
    Public Function Modificar(ByVal Sorteo As clsSorteo) As Boolean
        Dim Cn As New SqlConnection
        Dim Cm As New SqlCommand

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_MODI_SORTEO"

            Cm.Parameters.Add("@sort_id", SqlDbType.Int)
            Cm.Parameters.Add("@fecha", SqlDbType.SmallDateTime)
            Cm.Parameters.Add("@prod_id", SqlDbType.Int)
            Cm.Parameters.Add("@cli_id", SqlDbType.Int)
            Cm.Parameters.Add("@estado", SqlDbType.VarChar)
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)

            Cm.Parameters("@sort_id").Value = Sorteo.Prod_Id
            Cm.Parameters("@fecha").Value = Sorteo.Fecha
            Cm.Parameters("@prod_id").Value = Sorteo.Prod_Id
            Cm.Parameters("@cli_id").Value = Sorteo.Cli_Id
            Cm.Parameters("@estado").Value = Sorteo.Estado
            Cm.Parameters("@Cod_Suc").Value = Sorteo.Cod_Suc

            Cm.Connection = Cn

            Cm.ExecuteNonQuery()
            Cn.Close()
            Return True

        Catch ex As Exception
            Return False
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer un Sorteo por ID'
    Public Function TraeSorteo(ByVal sort_id As Integer) As clsSorteo
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim Sorteo As New clsSorteo()

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_SORTEO"

            Cm.Parameters.Add("@sort_id", SqlDbType.Int)
            Cm.Parameters("@sort_id").Value = sort_id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Sorteo.Sort_Id = Dr.GetInt32(0)
                    Sorteo.Fecha = Dr.GetDateTime(1)
                    Sorteo.Prod_Id = Dr.GetInt32(2)
                    Sorteo.Cli_Id = Dr.GetInt32(3)
                    Sorteo.Estado = Dr.GetString(4).Trim.ToUpper
                    Sorteo.Cod_Suc = Dr.GetInt32(5)

                    Dim Catalogo_Dao As New daoCatalogo()
                    Sorteo.Producto = Catalogo_Dao.TraeArticulo(Sorteo.Prod_Id)
                    Catalogo_Dao.Dispose()

                    Dim Cliente_Dao As New daoCliente()
                    Sorteo.Cliente = Cliente_Dao.TraeCliente(Sorteo.Cli_Id)
                    Cliente_Dao.Dispose()

                End While

                Dr.Close()
                Cn.Close()
            End If
            Return Sorteo
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todas los Sorteos por Rango de Fecha'
    Public Function TraeSorteos(ByVal fec_Desde As Date, ByVal fec_Hasta As Date) As List(Of clsSorteo)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaSorteo As New List(Of clsSorteo)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_SORTEO_X_FECHA"

            Cm.Parameters.Add("@fecha_desde", SqlDbType.SmallDateTime)
            Cm.Parameters.Add("@fecha_hasta", SqlDbType.SmallDateTime)
            Cm.Parameters("@fecha_desde").Value = fec_Desde
            Cm.Parameters("@fecha_hasta").Value = fec_Hasta

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Sorteo As New clsSorteo()
                    Sorteo.Sort_Id = Dr.GetInt32(0)
                    Sorteo.Fecha = Dr.GetDateTime(1)
                    Sorteo.Prod_Id = Dr.GetInt32(2)
                    Sorteo.Cli_Id = Dr.GetInt32(3)
                    Sorteo.Estado = Dr.GetString(4).Trim.ToUpper
                    Sorteo.Cod_Suc = Dr.GetInt32(5)

                    Dim Catalogo_Dao As New daoCatalogo()
                    Sorteo.Producto = Catalogo_Dao.TraeArticulo(Sorteo.Prod_Id)
                    Catalogo_Dao.Dispose()

                    Dim Cliente_Dao As New daoCliente()
                    Sorteo.Cliente = Cliente_Dao.TraeCliente(Sorteo.Cli_Id)
                    Cliente_Dao.Dispose()

                    ListaSorteo.Add(Sorteo)
                    Sorteo.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaSorteo
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todos los Sorteos por Cliente'
    Public Function TraeSorteos(ByVal Cliente As clsCliente) As List(Of clsSorteo)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaSorteo As New List(Of clsSorteo)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_SORTEO_X_CLIENTE"

            Cm.Parameters.Add("@cli_id", SqlDbType.Int)
            Cm.Parameters("@cli_id").Value = Cliente.Cli_Id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Sorteo As New clsSorteo()
                    Sorteo.Sort_Id = Dr.GetInt32(0)
                    Sorteo.Fecha = Dr.GetDateTime(1)
                    Sorteo.Prod_Id = Dr.GetInt32(2)
                    Sorteo.Cli_Id = Dr.GetInt32(3)
                    Sorteo.Estado = Dr.GetString(4).Trim.ToUpper
                    Sorteo.Cod_Suc = Dr.GetInt32(5)

                    Dim Catalogo_Dao As New daoCatalogo()
                    Sorteo.Producto = Catalogo_Dao.TraeArticulo(Sorteo.Prod_Id)
                    Catalogo_Dao.Dispose()

                    Dim Cliente_Dao As New daoCliente()
                    Sorteo.Cliente = Cliente_Dao.TraeCliente(Sorteo.Cli_Id)
                    Cliente_Dao.Dispose()

                    ListaSorteo.Add(Sorteo)
                    Sorteo.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaSorteo
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todos los Sorteos por Articulo'
    Public Function TraeSorteos(ByVal Articulo As clsCatalogo) As List(Of clsSorteo)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaSorteo As New List(Of clsSorteo)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_SORTEO_X_ARTICULO"

            Cm.Parameters.Add("@prod_id", SqlDbType.Int)
            Cm.Parameters("@prod_id").Value = Articulo.Prod_Id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Sorteo As New clsSorteo()
                    Sorteo.Sort_Id = Dr.GetInt32(0)
                    Sorteo.Fecha = Dr.GetDateTime(1)
                    Sorteo.Prod_Id = Dr.GetInt32(2)
                    Sorteo.Cli_Id = Dr.GetInt32(3)
                    Sorteo.Estado = Dr.GetString(4).Trim.ToUpper
                    Sorteo.Cod_Suc = Dr.GetInt32(5)

                    Dim Catalogo_Dao As New daoCatalogo()
                    Sorteo.Producto = Catalogo_Dao.TraeArticulo(Sorteo.Prod_Id)
                    Catalogo_Dao.Dispose()

                    Dim Cliente_Dao As New daoCliente()
                    Sorteo.Cliente = Cliente_Dao.TraeCliente(Sorteo.Cli_Id)
                    Cliente_Dao.Dispose()

                    ListaSorteo.Add(Sorteo)
                    Sorteo.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaSorteo
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todos los Sorteos '
    Public Function TraeSorteos() As List(Of clsSorteo)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaSorteo As New List(Of clsSorteo)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_SORTEO_TODOS"

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Sorteo As New clsSorteo()
                    Sorteo.Sort_Id = Dr.GetInt32(0)
                    Sorteo.Fecha = Dr.GetDateTime(1)
                    Sorteo.Prod_Id = Dr.GetInt32(2)
                    Sorteo.Cli_Id = Dr.GetInt32(3)
                    Sorteo.Estado = Dr.GetString(4).Trim.ToUpper
                    Sorteo.Cod_Suc = Dr.GetInt32(5)

                    Dim Catalogo_Dao As New daoCatalogo()
                    Sorteo.Producto = Catalogo_Dao.TraeArticulo(Sorteo.Prod_Id)
                    Catalogo_Dao.Dispose()

                    Dim Cliente_Dao As New daoCliente()
                    Sorteo.Cliente = Cliente_Dao.TraeCliente(Sorteo.Cli_Id)
                    Cliente_Dao.Dispose()

                    ListaSorteo.Add(Sorteo)
                    Sorteo.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaSorteo
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    '---------------------------------------------------------------------
    ' Constructor de la Clase
    '---------------------------------------------------------------------
    Public Sub New()

        MyBase.New()

    End Sub

    '---------------------------------------------------------------------
    ' Destructor de la Clase
    '---------------------------------------------------------------------
    Public Sub Dispose()

        MyBase.Finalize()

    End Sub
End Class
